package com.rybbaby.tss.front.tssStatistics.dao.imp;

import com.rybbaby.tss.core.bean.PageBean;
import com.rybbaby.tss.core.utils.BeanUtils;
import com.rybbaby.tss.core.web.dao.imp.GenericDao;
import com.rybbaby.tss.front.tssStatistics.dao.IViewClassesAttendceDao;
import com.rybbaby.tss.front.tssStatistics.entity.ViewClassesAttendce;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @description 员工表数据库操作实现
 * 
 * @author 雷春琼
 * 
 * @createDate 2017-08-10 上午12:25:24
 */
@Repository
public class ViewClassesAttendceDaoImp extends GenericDao<ViewClassesAttendce> implements IViewClassesAttendceDao {


    @Override
    public PageBean pageList(HashMap<String, Object> paramHashMap, int pageNo, int pageSize) throws InstantiationException, IllegalAccessException {
        String roomId = (String)paramHashMap.get("roomId");
        String adminUserId = (String)paramHashMap.get("adminUserId");
        String systemId = (String)paramHashMap.get("systemId");
        String type = (String)paramHashMap.get("type");
        String classTime_begin = (String)paramHashMap.get("classTime_begin");
        String classTime_end = (String)paramHashMap.get("classTime_end");
        String startTime_begin = (String)paramHashMap.get("startTime_begin");
        String endTime_end = (String)paramHashMap.get("endTime_end");

        String roomIdSql = "";
        String adminUserIdSql = "";
        String systemIdSql = "";
        String classTime_beginSql = "";
        String classTime_endSql = "";
        String startTime_beginSql = "";
        String endTime_endSql = "";

        if(roomId!=null && !roomId.equals("")){
            roomIdSql = "\tAND tss_class_room.id = '"+roomId+"'\n";
        }
        if(adminUserId!=null && !adminUserId.equals("")){
            adminUserIdSql = "\tAND sys_admin_user.id in ('"+adminUserId+"')\n";
        }
        if(systemId!=null && !systemId.equals("")){
            systemIdSql = "\tAND tss_course.systemId = '"+roomId+"'\n";
        }
        if(classTime_begin!=null && !classTime_begin.equals("")){
            classTime_beginSql = "\tAND unix_timestamp(tss_class_lessons.classTime) >= unix_timestamp('"+classTime_begin+"')\n";
        }
        if(startTime_begin!=null && !startTime_begin.equals("")){
            classTime_endSql = "\tAND unix_timestamp(tss_class_lessons.classTime) >= unix_timestamp('"+classTime_end+"')\n";
        }
        if(startTime_begin!=null && !startTime_begin.equals("")){
            startTime_beginSql = "\tAND unix_timestamp(tss_class_lessons.startTime) >= unix_timestamp('"+startTime_begin+"')\n";
        }
        if(endTime_end!=null && !endTime_end.equals("")){
            endTime_endSql = "\tAND unix_timestamp(tss_class_lessons.endTime) <= unix_timestamp('"+endTime_end+"')\n";
        }

        String sql = "SELECT\n" +
                "\ttss_class_lessons.id,\n" +
                "\ttss_classes.type,\n" +
                "\ttss_course.systemId,\n" +
                "\tsys_course_system.`name` AS courseName,\n" +
                "\tsys_admin_user.`realName` AS realName,\n" +
                "\ttss_class_lessons.roomId,\n" +
                "\ttss_class_lessons.classTime,\n" +
                "\ttss_class_lessons.startTime,\n" +
                "\ttss_class_lessons.endTime,\n" +
                "\ttss_class_room.`name` AS roomName,\n" +
                "\tCOUNT(\n" +
                "\t\ttss_member_attend_class_record.id\n" +
                "\t) AS sumShouldAttend,\n" +
                "\tCOUNT(\n" +
                "\t\tIF (\n" +
                "\t\t\ttss_member_check_attendance.attendenceStatus = 1,\n" +
                "\t\t\tTRUE,\n" +
                "\t\t\tNULL\n" +
                "\t\t)\n" +
                "\t) AS sumAttendance\n" +
                "FROM\n" +
                "\ttss_class_lessons\n" +
                "LEFT JOIN tss_classes ON tss_class_lessons.classId = tss_classes.id\n" +
                "LEFT JOIN sys_admin_user ON sys_admin_user.id = tss_class_lessons.adminUserId\n" +
                "LEFT JOIN tss_course ON tss_classes.courseId = tss_course.id\n" +
                "LEFT JOIN sys_course_system ON tss_course.systemId = sys_course_system.id\n" +
                "LEFT JOIN tss_class_room ON tss_class_lessons.roomId = tss_class_room.id\n" +
                "LEFT JOIN tss_member_attend_class_record ON tss_class_lessons.id = tss_member_attend_class_record.lessonsId\n" +
                "LEFT JOIN tss_member_check_attendance ON tss_member_attend_class_record.id = tss_member_check_attendance.attendClassId\n" +
                "WHERE\n" +
                "\ttss_class_lessons.`status` = 1\n" +
                "AND tss_member_attend_class_record.`status` = 1\n" +
                "AND tss_member_attend_class_record.isAttendClass = 1\n" +
                "AND tss_member_attend_class_record.isReturnSection = 0\n" +
                adminUserIdSql +
                roomIdSql +
                systemIdSql +
                classTime_beginSql +
                classTime_endSql +
                startTime_beginSql +
                endTime_endSql +
                "AND tss_classes.type = "+type+"\n" +

                "GROUP BY\n" +
                "\ttss_class_lessons.id";
//        "#AND tss_class_room.id = ''\n" +
//        "#AND\ttss_course.systemId = ''\n" +
//        "#AND unix_timestamp(tss_class_lessons.classTime) >= unix_timestamp('2017-12-20 20:18:07')\n" +
//        "#AND unix_timestamp(tss_class_lessons.classTime) <= unix_timestamp('2017-12-20 20:34:34')\n" +
//        "#AND unix_timestamp(tss_class_lessons.startTime) >= unix_timestamp('2017-12-20 20:18:07')\n" +
//        "#AND unix_timestamp(tss_class_lessons.endTime) <= unix_timestamp('2017-12-20 20:34:34')\n" +
        System.out.println(sql);
        PageBean pageBean = this.pageList(sql, pageNo, pageSize);
        List<ViewClassesAttendce> populate = BeanUtils.populate(ViewClassesAttendce.class, (List<Map<String, Object>>) pageBean.getList(), "attendanceRate");
        pageBean.setList(populate);
        return pageBean;
    }
}
